package org.mine.database.migration;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MapiJdbcRead {
	final static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	final static String url = "jdbc:sqlserver://192.168.68.129:4002; DatabaseName=mapi";
	final static String user = "xieweili_w";
	final static String pwd = "ppjyG9xfecQpm_j";

	static Connection con = null;
	static PreparedStatement statement = null;

	static {
		try {
			Class.forName(driver);
			con = DriverManager.getConnection(url, user, pwd);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}

	public DataModel readData(String tableName, String primaryKey, long startIndex, long size) throws Exception {
		String sql = "select top " + size + " *  from mapi.dbo." + tableName + " where " + primaryKey + " > "
				+ startIndex + " order by " + primaryKey + " asc";
		statement = con.prepareStatement(sql);
		return resultsetToModel(statement.executeQuery(), tableName, primaryKey);
	}
	
	public long count(String tableName, String primaryKey, long startIndex) throws Exception {
		String sql = "select count(" + primaryKey + ")  from mapi.dbo." + tableName + " where " + primaryKey + " > "
				+ startIndex;
		ResultSet resultSet = con.createStatement().executeQuery(sql);
		if(resultSet.next()){
			return resultSet.getLong(1);
		}else{
			throw new Exception();
		}
	}

	private DataModel resultsetToModel(ResultSet rs, String tableName, String primaryKey) throws SQLException {
		DataModel model = new DataModel();
		ResultSetMetaData rsmd = rs.getMetaData();
		List<String> columnName = new ArrayList<>();
		int count = rsmd.getColumnCount() + 1;
		model.setCloumnNum(count);

		StringBuilder sb = new StringBuilder();
		sb.append("insert into ");
		sb.append(tableName);
		StringBuilder sqlData = new StringBuilder();

		for (int j = 1; j < count; j++) {
			String name = rsmd.getColumnName(j);
			if(primaryKey.equalsIgnoreCase(name)){
				columnName.add(0, name);
			}else{
				columnName.add(name);
			}
		}
		
		for(int j = 0; j < columnName.size(); j++){
			if (j == 0)
				sb.append(" (").append("`" + columnName.get(j) + "`");
			else
				sb.append(",").append("`" + columnName.get(j) + "`");
			sqlData.append(j == 0 ? "" : ",");
			sqlData.append("?");
		}
		

		sb.append(") ").append("\n").append("values (").append(sqlData).append(")");
		model.setSql(sb.toString());

		List<List<Object>> datas = new ArrayList<>();
		while (rs.next()) {
			List<Object> thisdata = new ArrayList<>();
			columnName.forEach(x -> {
				try {
					Object data = rs.getObject(x);
					thisdata.add(data);
				} catch (SQLException e) {
					e.printStackTrace();
				}
			});
			datas.add(thisdata);
		}
		model.setData(datas);

		return model;
	}
}